-- @owner: ningyali
-- @date: 2024-08-12
-- @testpoint: select a,count(column) column列有default值（非空）

--step1:创建测试表; expect: 执行成功
drop table if exists t_ustore_count_optim_0011;
create table t_ustore_count_optim_0011(
c_id int  primary key, c_int  integer, c_bool bool,
c_real real,
c_char char(300),c_varchar2 varchar2(4000) default lpad('ee2t2xnbzzkih',80,'etskbfw@p%my8tesjbemus6gnv3'),
c_timestamp timestamp not null
) with (storage_type=ustore);

--step2:创建序列; expect: 执行成功
drop sequence if exists seq_ustore_count_optim_0011;
create sequence seq_ustore_count_optim_0011 increment by 1 start with 100000;

--step3:插入数据; expect: 执行成功
begin
	for i in 1..1000 loop
	insert into t_ustore_count_optim_0011(c_id,c_int,c_bool,c_real,c_char,c_timestamp) values(seq_ustore_count_optim_0011.nextval,i+3,'true',i%16+4.9690546872 ,lpad('ee2t2xnbzzkih',50,'et'),to_timestamp('19-aug-2004 21:38:09.123000','dd-mon-yyyy hh24:mi:ss.ff') );
    end loop;
end;
/

--step4:a列无索引; expect: 执行成功
select c_timestamp, count(c_varchar2) over(partition by c_timestamp ) from t_ustore_count_optim_0011  limit 5;
explain analyze select c_timestamp, count(c_varchar2) over(partition by c_timestamp ) from t_ustore_count_optim_0011 limit 5;

--step5:a列混合 expect: 执行成功
select c_timestamp,c_int, count(c_varchar2) over(partition by c_timestamp order by c_int) from t_ustore_count_optim_0011 order by 1,2,3  limit 5;
explain analyze select c_timestamp,c_int, count(c_varchar2) over(partition by c_timestamp order by c_int) from t_ustore_count_optim_0011 order by 1,2,3  limit 5;
explain analyze select c_timestamp,c_int, count(*) over(partition by c_timestamp order by c_int) from t_ustore_count_optim_0011 order by 1,2,3  limit 5;

--step6:a列有索引(混合索引); expect: 执行成功
select c_int,count(c_varchar2) over(partition by 1 order by c_int) from t_ustore_count_optim_0011 order by c_int desc limit 5;
explain analyze select c_int,count(c_varchar2) over(partition by 1 order by c_int) from t_ustore_count_optim_0011 order by c_int desc limit 5;
explain analyze select c_int,count(*) over(partition by 1 order by c_int) from t_ustore_count_optim_0011 order by c_int desc limit 5;

--step7:a列有索引; expect: 执行成功
explain analyze select c_char,count(*) over(partition by 1 order by c_int) from t_ustore_count_optim_0011 order by c_int desc limit 5;

--step8:清理环境; expect: 执行成功
drop table if exists t_ustore_count_optim_0011;
drop sequence if exists seq_ustore_count_optim_0011;